#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>

sqlite3 *db;

// 成绩录入：向 scores 表插入数据
int add_score(const char *sno, const char *subject, float score) {
    char sql[256];
    sprintf(sql, "INSERT INTO scores (sno, subject, score) VALUES ('%s', '%s', %f)", sno, subject, score);
    char *err_msg = NULL;
    int result = sqlite3_exec(db, sql, NULL, 0, &err_msg);
    if (result != SQLITE_OK) {
        printf("成绩录入失败：%s\n", err_msg);
        sqlite3_free(err_msg);
    }
    return result;
}

// 成绩查询：根据学号和科目查询成绩
int query_score(const char *sno, const char *subject) {
    char sql[256];
    sprintf(sql, "SELECT score FROM scores WHERE sno = '%s' AND subject = '%s'", sno, subject);
    sqlite3_stmt *stmt;
    int result = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            float score = sqlite3_column_double(stmt, 0);
            printf("查询结果：学号 %s，科目 %s，成绩 %.2f\n", sno, subject, score);
            sqlite3_finalize(stmt);
            return 1; // 查询到数据
        }
        sqlite3_finalize(stmt);
    }
    printf("未查询到学号 %s 科目 %s 的成绩\n", sno, subject);
    return 0; // 未查询到数据
}

// 成绩统计：统计班级某科目的平均分、最高分、最低分
void stats_score(const char *class_name, const char *subject) {
    char sql[256];
    // 关联 students 和 scores 表，筛选班级和科目数据
    sprintf(sql, "SELECT AVG(score), MAX(score), MIN(score) FROM scores "
                 "JOIN students ON scores.sno = students.sno "
                 "WHERE students.class = '%s' AND scores.subject = '%s'", class_name, subject);
    sqlite3_stmt *stmt;
    int result = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            double avg = sqlite3_column_double(stmt, 0);
            double max = sqlite3_column_double(stmt, 1);
            double min = sqlite3_column_double(stmt, 2);
            printf("班级 %s 科目 %s 统计结果：\n", class_name, subject);
            printf("平均分：%.2f，最高分：%.2f，最低分：%.2f\n", avg, max, min);
        } else {
            printf("班级 %s 科目 %s 无成绩数据\n", class_name, subject);
        }
        sqlite3_finalize(stmt);
    } else {
        printf("统计查询失败：%s\n", sqlite3_errmsg(db));
    }
}

// 初始化数据库表（若不存在则创建）
void init_db() {
    char *err_msg = NULL;
    // 创建 students 表
    const char *create_students = "CREATE TABLE IF NOT EXISTS students ("
                                  "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                                  "name TEXT NOT NULL,"
                                  "sno TEXT NOT NULL UNIQUE,"
                                  "class TEXT NOT NULL)";
    sqlite3_exec(db, create_students, NULL, 0, &err_msg);
    if (err_msg) {
        printf("创建 students 表失败：%s\n", err_msg);
        sqlite3_free(err_msg);
    }
    // 创建 scores 表
    const char *create_scores = "CREATE TABLE IF NOT EXISTS scores ("
                                "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                                "sno TEXT NOT NULL,"
                                "subject TEXT NOT NULL,"
                                "score REAL NOT NULL,"
                                "FOREIGN KEY (sno) REFERENCES students(sno))";
    sqlite3_exec(db, create_scores, NULL, 0, &err_msg);
    if (err_msg) {
        printf("创建 scores 表失败：%s\n", err_msg);
        sqlite3_free(err_msg);
    }
}

int main() {
    // 打开数据库（不存在则创建）
    int open_result = sqlite3_open("student.db", &db);
    if (open_result != SQLITE_OK) {
        printf("打开数据库失败：%s\n", sqlite3_errmsg(db));
        return -1;
    }
    // 初始化数据库表
    init_db();

    // 测试成绩录入（需先确保 students 表有对应 sno 的学生！）
    // 注意：当前代码未录入学生信息，直接录入成绩会因外键约束失败！
    // 解决方案：手动插入学生信息，或扩展代码支持学生录入
    // 手动插入学生（临时解决测试问题）：
    char *err_msg = NULL;
    sqlite3_exec(db, "INSERT INTO students (name, sno, class) VALUES ('张三', 'S001', '一班')", NULL, 0, &err_msg);
    if (err_msg) { printf("插入学生失败：%s\n", err_msg); sqlite3_free(err_msg); }
    sqlite3_exec(db, "INSERT INTO students (name, sno, class) VALUES ('李四', 'S002', '一班')", NULL, 0, &err_msg);
    if (err_msg) { printf("插入学生失败：%s\n", err_msg); sqlite3_free(err_msg); }

    // 重新测试成绩录入（此时学生存在，可成功）
    add_score("S001", "数学", 90.5);
    add_score("S002", "数学", 85.0);
    add_score("S001", "语文", 88.0);
    add_score("S002", "语文", 92.5);

    // 测试成绩查询
    query_score("S001", "数学");
    query_score("S002", "语文");

    // 测试成绩统计（统计一班数学成绩）
    stats_score("一班", "数学");

    // 关闭数据库
    sqlite3_close(db);
    return 0;
}
